﻿using System;
using System.Collections;
using System.Collections.Generic;

using System.Data;
using System.Data.Common;

using System.Data.SqlClient;
using System.Data.SqlTypes;

using System.IO;
using System.Net;
using System.Net.Security;

using System.Security;
using System.Security.Cryptography;
using System.Security.Cryptography.X509Certificates;
using System.Security.Cryptography.Xml;

using System.Security.Policy;

using System.Web;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    /// <summary>
    /// The UploadTable function with extract data from a specified SQL table/view/function
    /// in a given format, writting the file on a remote FTP/FTPS server.
    /// </summary>
    /// <param name="ftpServerPath">ftpServerPath is a fully qualified path and filename of where the data will be uploaded</param>
    /// <param name="ftpUsername">ftpUsername is the username for the ftp server account under which the SQLCLR will connect, this may be null</param>
    /// <param name="ftpPassword">ftpPassword is the password for the given user account</param>
    /// <param name="ftpUseSSL">ftpUseSSL is a boolean flag, if true the client connection will be FTPS</param>
    /// <param name="exportTable">exportTable holds the name of the table/view/function containing the data you wish to be exported and uploaded to the ftpServer</param>
    /// <param name="exportType">This is not currently used, but could hold a value for the type of data file you wish to write on the ftpServer</param>
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void UploadTable
        (
            SqlString ftpServerPath,   /// --- ftp.thinkpositive.com/public/upload/PS_JOIN_LEAVE_{dd.mmm.yyyy}.csv
            SqlString ftpUsername,
            SqlString ftpPassword,
            SqlBoolean ftpUseSSL,
            SqlString exportTable,
            SqlInt32 exportType		/// --- export file type
        )
    {
        // --- 
        SqlPipe _sqlPipe = SqlContext.Pipe;
        using (SqlConnection _sqlConn = new SqlConnection("context connection=true"))
        {
            _sqlConn.Open();

            SqlCommand _sqlComm = new SqlCommand(String.Format("SELECT * FROM {0}", exportTable.Value), _sqlConn);
            SqlDataReader _dataReader = _sqlComm.ExecuteReader();

            // ---
            try
            {
                ServicePointManager.ServerCertificateValidationCallback = AcceptAllCertificatePolicy;

                FtpWebRequest _ftpRequest = (FtpWebRequest)WebRequest.Create(ftpServerPath.Value);
                _ftpRequest.EnableSsl = (ftpUseSSL.IsTrue ? true : false);
                if (!ftpUsername.IsNull)
                    _ftpRequest.Credentials = new NetworkCredential(ftpUsername.Value, ftpPassword.Value);
                _ftpRequest.Method = WebRequestMethods.Ftp.UploadFile;

                // --- 
                using (Stream _destinationStream = _ftpRequest.GetRequestStream())
                {
                    writeCSV(_dataReader, _destinationStream);
                }

                // --- 
                using (FtpWebResponse _ftpResponse = (FtpWebResponse)_ftpRequest.GetResponse())
                {
                    _sqlPipe.Send(String.Format("Response status: {0}", _ftpResponse.StatusDescription));
                }
            }
            catch (System.Net.WebException wex)
            {
                _sqlPipe.Send(wex.Message);
            }
            _sqlConn.Close();
        }
    }

    /// <summary>
    /// </summary>
    /// <param name="_sourceData"></param>
    /// <param name="_exportStream"></param>
    private static void writeCSV(SqlDataReader _sourceData, Stream _exportStream)
    {

        SqlPipe _sqlPipe = SqlContext.Pipe;

        // --- header
        List<string> _columnNames = new List<string>();
        for (int _dataColumn = 0; _dataColumn < _sourceData.FieldCount - 1; _dataColumn++)
            _columnNames.Add(_sourceData.GetName(_dataColumn));

        String _header = String.Join(",", _columnNames.ToArray());
        _header = String.Format("{0}\r\n", _header);

        byte[] _baHeader = System.Text.ASCIIEncoding.ASCII.GetBytes(_header);

        // --- write header to stream
        _sqlPipe.Send(_header);
        _exportStream.Write(_baHeader, 0, _baHeader.Length);

        // -- body
        while (_sourceData.Read())
        {
            List<string> _columnValues = new List<string>();
            for (int _dataColumn = 0; _dataColumn < _sourceData.FieldCount - 1; _dataColumn++)
            {
                if (_sourceData.GetDataTypeName(_dataColumn) == "varchar")
                    _columnValues.Add(String.Format("\"{0}\"", _sourceData.GetValue(_dataColumn).ToString()));
                else
                    _columnValues.Add(_sourceData.GetValue(_dataColumn).ToString());
            }
            // -- write data row to stream
            #region build and write record
            String _record = String.Join(",", _columnValues.ToArray());
            _record = String.Format("{0}\r\n", _record);

            Byte[] _baRecord = System.Text.ASCIIEncoding.ASCII.GetBytes(_record);

            _sqlPipe.Send(_record);

            _exportStream.Write(_baRecord, 0, _baRecord.Length);

            #endregion

            _columnValues.Clear();

        }

    }

    /// <summary>
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="certificate"></param>
    /// <param name="chain"></param>
    /// <param name="sslPolicyErrors"></param>
    /// <returns></returns>
    private static bool AcceptAllCertificatePolicy(object sender, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
    {
        return true;
    }

};